﻿USE ASERDB
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetBillNo')
BEGIN
	DROP  Procedure  GetBillNo
END
GO
CREATE Procedure GetBillNo(
   @aTable_nam varchar(20)--,
   --@aNewBill_no varchar(12) output
 )AS
declare @lBill_no int;
declare @lBill_nostr varchar(10);
declare @lOldDate datetime ;
declare @Bill_NoCount int;
declare @lNewDate datetime ;
declare @aNewBill_no varchar(20);
declare @lPreFix varchar(3);
declare @lline_id int

declare @lstr varchar(6);
select @lstr=null

select @lPreFix=PreFix from PreFixTable where TableName=@aTable_nam

select @lNewDate= Convert(datetime ,convert(char(10),getdate(),101 ))

select @Bill_NoCount= count(*) 
  from NoRecordor 
 where Table_Nam=@aTable_nam 

select @lBill_no=Bill_no,
       @lOldDate=Convert(datetime ,convert(char(10),LastbillDate,101)),
       @lline_id = line_id
  from NoRecordor where Table_Nam=@aTable_nam

if @Bill_NoCount =0 
begin
  insert into NoRecordor(Table_nam,LastBillDate,Bill_no,LastIdDate,Line_id)
    values(@aTable_nam, @lNewDate, 1,@lNewDate , 1)
  select @lBill_no = 1
end
else --if @Bill_NoCount=1
begin
  if @lNewDate>@lOldDate
  begin
    Update NoRecordor set Bill_no=1 ,LastbillDate=@lNewDate
    where Table_Nam=@aTable_nam
    select @lBill_no = 1
  end
  else if @lNewDate = @lOldDate
  begin
    Update NoRecordor set Bill_no=Bill_no+1 ,LastbillDate=@lNewDate
    where Table_Nam=@aTable_nam
    select @lBill_no = @lBill_no + 1
  end
  else
  begin
    Update NoRecordor set Bill_no=@lLine_id + 1,LastbillDate=@lNewDate
    where Table_Nam=@aTable_nam 
    select @lBill_no = @lLine_id + 1
  end 
end  
Update NoRecordor set Line_id = case when @lLine_id > @lBill_no then  @lLine_id else @lBill_no end
where Table_Nam=@aTable_nam

  if (@lBill_no>0) and (@lBill_no<10)  
    select @lBill_nostr='00000'+ convert(char(1),@lBill_no)
  else 
  if (@lBill_no>9) and (@lBill_no<100)  
    select @lBill_nostr='0000'+ convert(char(2),@lBill_no)
  else 
  if (@lBill_no>99) and (@lBill_no<1000)  
    select @lBill_nostr='000'+ convert(char(3),@lBill_no)
  else 
  if (@lBill_no>999) and (@lBill_no<10000)  
    select @lBill_nostr= '00'+convert(char(4),@lBill_no)
  if (@lBill_no > 9999) and (@lBill_no < 100000)  
    select @lstr = '0' + convert(char(5), @lBill_no)
  if (@lBill_no > 99999) and (@lBill_no < 1000000)  
    select @lstr = convert(char(6), @lBill_no)
  select @aNewBill_no = @lPreFix +  CONVERT(VARCHAR(4),@lNewDate,120) + RIGHT(CONVERT(VARCHAR(7),@lNewDate,120),2) + RIGHT(CONVERT(VARCHAR(10),@lNewDate,120),2) + @lBill_nostr 
  Select @aNewBill_no
GO

